﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;

namespace ISB_iCard
{
    class AllTxn
    {
        public string txnID;
        public string InstituteId;
        public bool Status;
        public bool IsError;
        public string CreateDate;
        
        public AllTxn(string txnId,string InstituteId, bool Status, string CreateDate, bool IsError)
        {
            this.txnID = txnId;
            this.InstituteId = InstituteId;
            this.Status = Status;
            this.CreateDate = CreateDate;
            this.IsError = IsError;
        }
    }
    class LocalDB
    {
        public LocalDB()
        {

        }
        public string ConnectionString = "Data Source=\\Program Files\\DeviceApplication4\\ISB.sdf";
        public void Push(long TxnID, string PeopleID, string StewardId, double Amount, int IsCash)
        {
            //DataTable data = null;
            using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                int recCount = 0;
                using (SqlCeCommand cmdSelect = connection.CreateCommand())
                {
                    cmdSelect.CommandText = "SELECT Count(1) FROM tblTxn WHERE TxnId = '" + TxnID +"'";
                    recCount = Convert.ToInt32(cmdSelect.ExecuteScalar());
                    connection.Close();
                }
                if (recCount == 0)
                {
                    using (SqlCeCommand cmdInsert = connection.CreateCommand())
                    {
                        connection.Open();
                        cmdInsert.CommandText = "INSERT tblTxn(TxnId, InstituteId, Amount, IsCash) Values (" + TxnID + ", '" + PeopleID + "', '" + Amount + "','" + IsCash + "')";
                        cmdInsert.ExecuteNonQuery();
                        connection.Close();
                    }
                }
                else
                    throw new Exception("Transaction Exists!");
            }
        }

        public void Pop(long TxnId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (SqlCeCommand cmdUpdate= connection.CreateCommand())
                {
                    cmdUpdate.CommandText = "Update tblTxn SET IsUpdated = 1 WHERE TxnID = '" + TxnId + "'";
                    cmdUpdate.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }
        
        public void UpdateError(long TxnId)
        {
            using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (SqlCeCommand cmdUpdate = connection.CreateCommand())
                {
                    cmdUpdate.CommandText = "Update tblTxn SET IsError = 1 WHERE TxnID = '" + TxnId + "'";
                    cmdUpdate.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }

        public List<AllTxn> All()
        {
            DataTable data = null;
            List<AllTxn> lstAll = new List<AllTxn>();
            using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (SqlCeCommand cmdSelect = connection.CreateCommand())
                {
                    cmdSelect.CommandText = "SELECT * FROM tblTxn WHERE IsUpdated = 0 and IsError = 0";
                    SqlCeDataReader dr = cmdSelect.ExecuteReader();
                    while (dr.Read())
                    {
                        lstAll.Add(new AllTxn(Convert.ToString(dr["TxnId"]), Convert.ToString(dr["InstituteId"]), Convert.ToBoolean(dr["IsUpdated"]), Convert.ToString(dr["CreateDate"]), Convert.ToBoolean(dr["IsError"])));
                    }
                    connection.Close();
                }
                return lstAll;
            }
        }
    }
}
